package net.ojbk.dao;
import java.sql.*;

import net.ojbk.database.DatabaseConnection;
import net.ojbk.po.ClassData;


public class ClassDataDao {
	static final String tableName="clasData";
	DatabaseConnection dbconn;
	Statement stmt;
	public ClassDataDao(){
		dbconn =new DatabaseConnection();
		stmt=dbconn.getStatement();
	}
	public void finalize(){
		dbconn.close();
	}
	
	public boolean addOneRecord(ClassData cd){
		boolean flag=false;
		try {
			String sql = "INSERT INTO "
					+ tableName
					+"(grade,college,clas)"
					+ " VALUE('"
					+ cd.getGrade()
					+ "', '"
					+ cd.getCollege()
					+ "', '"
					+ cd.getClas()
					+ "');";
			stmt.executeUpdate(sql);
			flag = true;
		} catch(Exception e) {
			e.printStackTrace();
		}
		return flag;
	}
	
	public boolean deleteOneRecord(ClassData cd){
		boolean flag=false;
		try {
			String sql="delete from "
					+ tableName
					+ " where grade='"
					+ cd.getGrade()
					+"' and"
					+ " college='"
					+ cd.getCollege()
					+"' and"
					+ " clas='"
					+ cd.getClas()
					+ "'";
			stmt.executeUpdate(sql);
			flag=true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
	}
	
	public boolean queryOneRecord(ClassData cd){  // 判断该班级是否真实有效
		boolean flag=false;
		ResultSet re;
		try {
			String sql="select * from "
					+tableName
					+ " where grade='"
					+ cd.getGrade()
					+"' and"
					+ " college='"
					+ cd.getCollege()
					+"' and"
					+ " clas='"
					+ cd.getClas()
					+ "'";
			re=stmt.executeQuery(sql);
			if(re.next()){
				cd.setClas(re.getString("clas"));
				cd.setCollege(re.getString("college"));
				cd.setGrade(re.getString("grade"));
				flag=true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
	}
	
	public int getCount() {
		int count = 0;
		ResultSet re;
		try {
			// SELECT COUNT(*) FROM tableName
			String sql="SELECT COUNT(*) FROM "
					+ tableName
					+ ";";
			re = stmt.executeQuery(sql);
			if (re.next()) {
				count = re.getInt(1);
			}
		} catch(Exception e) {
			e.printStackTrace();
		}
		return count;
	}
	
	public ClassData[] queryAllRecord() {
		int count = this.getCount();
		ClassData[] cds=new ClassData[count+5]; // 防止
		ResultSet re;
		ClassData cd;
		try {
			String sql="SELECT * FROM "
					+ tableName
					+ ";";
			re = stmt.executeQuery(sql); 
			for (int i=0; re.next(); i++) {
				cd = new ClassData();
				cd.setClas(re.getString("clas"));
				cd.setCollege(re.getString("college"));
				cd.setGrade(re.getString("grade"));
				cds[i] = cd;
			}
		} catch(Exception e) {
			e.printStackTrace();
		}
		return cds;
	}
	public ClassData[] queryCollege(ClassData cd) {
		int count = this.getCount();
		ClassData[] cds=new ClassData[count+5]; // 防止
		ResultSet re;
		ClassData cdd;
		try {
			String sql="SELECT * FROM "
					+ tableName
					+ " where college='"+cd.getCollege()+"';";
			re = stmt.executeQuery(sql); 
			for (int i=0; re.next(); i++) {
				cdd = new ClassData();
				cdd.setClas(re.getString("clas"));
				cdd.setCollege(re.getString("college"));
				cdd.setGrade(re.getString("grade"));
				cds[i] = cdd;
			}
		} catch(Exception e) {
			e.printStackTrace();
		}
		return cds;
	}
	public ClassData[] queryGrade(ClassData cd) {
		int count = this.getCount();
		ClassData[] cds=new ClassData[count+5]; // 防止
		ResultSet re;
		ClassData cdd;
		try {
			String sql="SELECT * FROM "
					+ tableName
					+ " where college='"+cd.getCollege()+"' and grade='"+cd.getGrade()+"';";
			re = stmt.executeQuery(sql); 
			for (int i=0; re.next(); i++) {
				cdd = new ClassData();
				cdd.setClas(re.getString("clas"));
				cdd.setCollege(re.getString("college"));
				cdd.setGrade(re.getString("grade"));
				cds[i] = cdd;
			}
		} catch(Exception e) {
			e.printStackTrace();
		}
		return cds;
	}
	public ClassData[] queryClas(ClassData cd) {
		int count = this.getCount();
		ClassData[] cds=new ClassData[count+5]; // 防止
		ResultSet re;
		ClassData cdd;
		try {
			String sql="SELECT * FROM "
					+ tableName
					+ " where college='"+cd.getCollege()+"' and grade='"+cd.getGrade()+"' and clas='"+cd.getClas()+"';";
			re = stmt.executeQuery(sql); 
			for (int i=0; re.next(); i++) {
				cdd = new ClassData();
				cdd.setClas(re.getString("clas"));
				cdd.setCollege(re.getString("college"));
				cdd.setGrade(re.getString("grade"));
				cds[i] = cdd;
			}
		} catch(Exception e) {
			e.printStackTrace();
		}
		return cds;
	}
	    public boolean deleteCollege(ClassData cd) {
	    boolean flag=false;
		try {
			String sql="delete from "
					+ tableName
					+ " where college='"+cd.getCollege()+"'";
			stmt.executeUpdate(sql); 
			flag=true;
		} catch(Exception e) {
			e.printStackTrace();
		}
		return flag;
	}
	    public boolean deleteGrade(ClassData cd) {
        boolean flag=false;
		try {
			String sql="delete from "
					+ tableName
					+ " where college='"+cd.getCollege()+"' and grade='"+cd.getGrade()+"'";
			stmt.executeUpdate(sql); 
			flag=true;
		} catch(Exception e) {
			e.printStackTrace();
		}
		return flag;
	}
	    public boolean deleteClas(ClassData cd) {
	    boolean flag=false;
		try {
			String sql="delete from "
					+ tableName
					+ " where college='"+cd.getCollege()+"' and grade='"+cd.getGrade()+"' and clas='"+cd.getClas()+"'";
			stmt.executeUpdate(sql); 
			flag=true;
		} catch(Exception e) {
			e.printStackTrace();
		}
		return flag;
	}
	
	public static void main(String[] args){
//		ClassDataDao cdd = new ClassDataDao();
//		System.out.println("123323");
//		System.out.println(cdd.queryAllRecord()[0].getCollege());
	}
}








